﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;

using ECCIT;
using ECCIT.Data;
using ECCIT.Business.DAL;

using OgilvyOne.UKNow.Globalization;
using OgilvyOne.UKNow.Utility;
using OgilvyOne.UKNow.Model;
using OgilvyOne.UKNow.IDAL;

namespace OgilvyOne.UKNow.DAL
{
    public class MemberDAL : UKNowBaseDAL, IDALMember
    {
        private const string TBL_NAME = "UKNOW_MEMBER_INFO";
        private const string COL_MEMBER_ID = "MEMBER_ID";
        private const string COL_USERNAME = "USERNAME";
        private const string COL_PASSWORD = "PASSWORD";
        private const string COL_MEMBER_NAME = "MEMBER_NAME";
        private const string COL_USER_TYPE = "USER_TYPE";
        private const string COL_MOBLIE = "MOBLIE";
        private const string COL_LAST_LOGIN_TIME = "LAST_LOGIN_TIME";
        private const string COL_REGISTERTYPE = "REGISTERTYPE";
        private const string COL_STATE = "STATE";
        private const string COL_EMAIL = "EMAIL";
        private const string COL_BIRTHDAY = "BIRTHDAY";
        private const string COL_STUDYABROAD = "STUDYABROAD";
        private const string COL_IELTS = "IELTS";
        private const string COL_ARTSANDCULTURE = "ARTSANDCULTURE";
        private const string COL_SINA = "SINA";
        private const string COL_SOHU = "SOHU";
        private const string COL_TENGXUN = "TENGXUN";
        private const string COL_SINAWEIBO = "SINAWEIBO";
        private const string COL_TENGXUNWEIBO = "TENGXUNWEIBO";
        private const string COL_KAIXIN = "KAIXIN";
        private const string COL_DOUBAN = "DOUBAN";
        private const string COL_RENREN = "RENREN";
        private const string COL_GOOGLE = "GOOGLE";
        private const string COL_BAIDU = "BAIDU";
        private const string COL_BING = "BING";
        private const string COL_OFFLINEEVENTS = "OFFLINEEVENTS";
        private const string COL_NEWSPAPER = "NEWSPAPER";
        private const string COL_OUTDOOR = "OUTDOOR";
        private const string COL_TAXI = "TAXI";
        private const string COL_METRO = "METRO";
        private const string COL_BBS = "BBS";
        private const string COL_TV = "TV";
        private const string COL_RECOMMENDATION = "RECOMMENDATION";
        private const string COL_OTHERCHANNELS = "OTHERCHANNELS";
        private const string COL_MEMBER_HEARD_IMG = "MEMBER_HERD_IMG";
        private const string MEMBER_INSERT_TIME = "MEMBER_INSERT_TIME";


        public override string TableName { get { return TBL_NAME; } }

        public override Type ModelType { get { return typeof(MemberModel); } }

        [PrimaryKey]
        public IField ID { get { return FieldFactory.Create(ModelType, "ID", ColumnFactory.CreateColumn(COL_MEMBER_ID, DataType.Int)); } }
        public IField AccountName { get { return FieldFactory.Create(ModelType, "AccountName", ColumnFactory.CreateColumn(COL_USERNAME, DataType.String, 200)); } }
        public IField Password { get { return FieldFactory.Create(ModelType, "Password", ColumnFactory.CreateColumn(COL_PASSWORD, DataType.String, 50)); } }
        public IField Name { get { return FieldFactory.Create(ModelType, "Name", ColumnFactory.CreateColumn(COL_MEMBER_NAME, DataType.String, 50)); } }
        public IField Type { get { return FieldFactory.Create(ModelType, "Type", ColumnFactory.CreateColumn(COL_USER_TYPE, DataType.String, 20)); } }
        public IField Mobile { get { return FieldFactory.Create(ModelType, "Mobile", ColumnFactory.CreateColumn(COL_MOBLIE, DataType.String, 20)); } }
        public IField LastLoginTime { get { return FieldFactory.Create(ModelType, "LastLoginTime", ColumnFactory.CreateColumn(COL_LAST_LOGIN_TIME, DataType.DateTime)); } }
        public IField RegisterType { get { return FieldFactory.Create(ModelType, "RegisterType", ColumnFactory.CreateColumn(COL_REGISTERTYPE, DataType.String, 20)); } }
        public IField State { get { return FieldFactory.Create(ModelType, "State", ColumnFactory.CreateColumn(COL_STATE, DataType.Int)); } }
        public IField Email { get { return FieldFactory.Create(ModelType, "Email", ColumnFactory.CreateColumn(COL_EMAIL, DataType.String, 200)); } }

        public IField Birthday { get { return FieldFactory.Create(ModelType, "Birthday", ColumnFactory.CreateColumn(COL_BIRTHDAY, DataType.String, 50)); } }
        public IField StudyAbroad { get { return FieldFactory.Create(ModelType, "StudyAbroad", ColumnFactory.CreateColumn(COL_STUDYABROAD, DataType.String, 50)); } }
        public IField IELTS { get { return FieldFactory.Create(ModelType, "IELTS", ColumnFactory.CreateColumn(COL_IELTS, DataType.String, 50)); } }
        public IField ArtsAndCulture { get { return FieldFactory.Create(ModelType, "ArtsAndCulture", ColumnFactory.CreateColumn(COL_ARTSANDCULTURE, DataType.String, 50)); } }
        public IField Sina { get { return FieldFactory.Create(ModelType, "Sina", ColumnFactory.CreateColumn(COL_SINA, DataType.String, 50)); } }
        public IField Sohu { get { return FieldFactory.Create(ModelType, "Sohu", ColumnFactory.CreateColumn(COL_SOHU, DataType.String, 50)); } }
        public IField TengXun { get { return FieldFactory.Create(ModelType, "TengXun", ColumnFactory.CreateColumn(COL_TENGXUN, DataType.String, 50)); } }
        public IField SinaWeibo { get { return FieldFactory.Create(ModelType, "SinaWeibo", ColumnFactory.CreateColumn(COL_SINAWEIBO, DataType.String, 50)); } }
        public IField TengXunWeibo { get { return FieldFactory.Create(ModelType, "TengXunWeibo", ColumnFactory.CreateColumn(COL_TENGXUNWEIBO, DataType.String, 50)); } }
        public IField KaiXin { get { return FieldFactory.Create(ModelType, "KaiXin", ColumnFactory.CreateColumn(COL_KAIXIN, DataType.String, 50)); } }
        public IField DouBan { get { return FieldFactory.Create(ModelType, "DouBan", ColumnFactory.CreateColumn(COL_DOUBAN, DataType.String, 50)); } }
        public IField RenRen { get { return FieldFactory.Create(ModelType, "RenRen", ColumnFactory.CreateColumn(COL_RENREN, DataType.String, 50)); } }
        public IField Google { get { return FieldFactory.Create(ModelType, "Google", ColumnFactory.CreateColumn(COL_GOOGLE, DataType.String, 50)); } }

        public IField Baidu { get { return FieldFactory.Create(ModelType, "Baidu", ColumnFactory.CreateColumn(COL_BAIDU, DataType.String, 50)); } }
        public IField Bing { get { return FieldFactory.Create(ModelType, "Bing", ColumnFactory.CreateColumn(COL_BING, DataType.String, 50)); } }
        public IField OfflineEvents { get { return FieldFactory.Create(ModelType, "OfflineEvents", ColumnFactory.CreateColumn(COL_OFFLINEEVENTS, DataType.String, 50)); } }
        public IField NewsPaper { get { return FieldFactory.Create(ModelType, "NewsPaper", ColumnFactory.CreateColumn(COL_NEWSPAPER, DataType.String, 50)); } }
        public IField Outdoor { get { return FieldFactory.Create(ModelType, "Outdoor", ColumnFactory.CreateColumn(COL_OUTDOOR, DataType.String, 50)); } }
        public IField Taxi { get { return FieldFactory.Create(ModelType, "Taxi", ColumnFactory.CreateColumn(COL_TAXI, DataType.String, 50)); } }
        public IField Metro { get { return FieldFactory.Create(ModelType, "Metro", ColumnFactory.CreateColumn(COL_METRO, DataType.String, 50)); } }
        public IField BBS { get { return FieldFactory.Create(ModelType, "BBS", ColumnFactory.CreateColumn(COL_BBS, DataType.String, 50)); } }
        public IField TV { get { return FieldFactory.Create(ModelType, "TV", ColumnFactory.CreateColumn(COL_TV, DataType.String, 50)); } }
        public IField Recommendation { get { return FieldFactory.Create(ModelType, "Recommendation", ColumnFactory.CreateColumn(COL_RECOMMENDATION, DataType.String, 50)); } }
        public IField OtherChannels { get { return FieldFactory.Create(ModelType, "OtherChannels", ColumnFactory.CreateColumn(COL_OTHERCHANNELS, DataType.String, 50)); } }

        public IField MemberImg { get { return FieldFactory.Create(ModelType, "MemberImg", ColumnFactory.CreateColumn(COL_MEMBER_HEARD_IMG, DataType.String, 200)); } }
        public IField InsertTime { get { return FieldFactory.Create(ModelType, "InsertTime", ColumnFactory.CreateColumn(MEMBER_INSERT_TIME, DataType.DateTime)); } }


        public override IField[] InsertFields { get { return new IField[] {  AccountName, Password, Name, Type, Mobile, LastLoginTime, RegisterType, State, Email, Birthday, StudyAbroad, IELTS, ArtsAndCulture, Sina, Sohu, TengXun, SinaWeibo, TengXunWeibo, KaiXin, DouBan, RenRen, Google, Baidu, Bing, OfflineEvents, NewsPaper, Outdoor, Taxi, Metro, BBS, TV, Recommendation, OtherChannels, MemberImg }; } }
        public override IField[] SelectFields { get { return new IField[] { ID, AccountName, Password, Name, Type, Mobile, LastLoginTime, RegisterType, State, Email, Birthday, StudyAbroad, IELTS, ArtsAndCulture, Sina, Sohu, TengXun, SinaWeibo, TengXunWeibo, KaiXin, DouBan, RenRen, Google, Baidu, Bing, OfflineEvents, NewsPaper, Outdoor, Taxi, Metro, BBS, TV, Recommendation, OtherChannels, MemberImg }; } }
        public override IField[] UpdateFields { get { return InsertFields; } }

        public MemberDAL() { }

        public MemberDAL(LanguageType languageType) { this.Language = LanguageFactory.Create(languageType); }

        #region IDALMember Members

        public MemberModel GetMemberModelByID(int? id)
        {
            MemberModel model = (MemberModel)GetModelByPrimaryKey(this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(this.ID, id) });
            return model;
        }

        public IList<MemberModel> GetAllMemberModels()
        {
            return GetModels(null, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Name.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }
        /* 添加人;陈鹏
         * 内容：用于验证用户名 和邮件的重复
         */
        public IList<MemberModel> Check( string username)
        {
            string whereClause = string.Format("where {0}='{1}' ",  COL_MEMBER_NAME, username);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(InsertTime.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }

        public IList<MemberModel> CheckEmail(string email)
        {
            string whereClause = string.Format("where {0}='{1}' ", COL_EMAIL, email);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(InsertTime.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }

        public IList<MemberModel> QueryTime(string staTime,string endTime)
        {
            string whereClause = string.Format("where datediff(dd, last_login_time, '{0}')<=0 and datediff(dd, last_login_time, '{1}')>=0", staTime, endTime);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(InsertTime.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }

        public ResultInfo SignIn(string memberEmail, string password)
        {
            ResultInfo result = new ResultInfo(false);

            IDataParameter[] parms = DBHelper.GetDataParameters(new IField[] { Email, Password });
            parms[0].Value = memberEmail;
            parms[1].Value = password;

            string whereClause = " WHERE  EMAIL=#EMAIL AND PASSWORD=#PASSWORD ";
            try
            {
                MemberModel modelInfo = (MemberModel)GetModelInfo(whereClause, parms);
                if (modelInfo == null)
                {
                    result.Result = false;
                }
                else
                {
                    result.Result = true;
                    result.Message = Language.GetAlertMessage(ModelType, Common.Enums.Action.SignIn, Common.Enums.ActionResult.Success);
                    result.ReturnObject = modelInfo;
                }
            }
            catch (Exception ex)
            {
                result.Message = Language.GetAlertMessage(ModelType, Common.Enums.Action.SignIn, Common.Enums.ActionResult.Exception);
            }

            return result;
        }

        public ResultInfo Save(MemberModel modelInfo)
        {
            if (modelInfo == null) throw new ArgumentNullException("modelInfo");

            ResultInfo result = new ResultInfo(false);

            try
            {
                int rowsAffected = 0;
                if (modelInfo.ID == null)   // insert
                {
                    //rowsAffected = Insert(modelInfo, null);
                    string cmdInsert = DBHelper.GetInsertCommandText(TBL_NAME, InsertFields) + "; select @@identity";
                    IDataParameter[] parms = DBHelper.GetDataParameters(InsertFields);
                    DBHelper.BindDataParametersValue(parms, InsertFields, modelInfo);
                    object o = DBHelper.ExecuteScalar(cmdInsert, parms);
                    if (o != null && o != DBNull.Value)
                        modelInfo.ID = Convert.ToInt32(o);
                    //
                    rowsAffected = 1;
                }
                else // update
                {
                    rowsAffected = UpdateByPrimaryKey(modelInfo, null);
                   
                }
                //问题出在这里，判断条件错了,已更改为在插入操作时值变为1
                if (rowsAffected > 0)
                {
                    result.Message = "会员数据保存成功！";
                    result.Result = true;
                    result.ReturnObject = modelInfo;
                }
            }
            catch (Exception ex)
            {
                result.Exception = ex;
                result.Message = string.Format("保存会员数据时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo Delete(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);
            using (IDbConnection conn = DBHelper.GetDbConnection())
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                try
                {
                    foreach (int? id in idList)
                        DeleteModelByPrimaryKey(trans, this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(this.ID, id) });

                    trans.Commit();
                    result.Result = true;
                    result.Message = "会员数据删除成功！";
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    result.Message = string.Format("删除会员数据时发生异常。异常信息：{0}", ex.Message);
                }
                finally
                {
                    trans.Dispose();
                }
            }

            return result;
        }
        #endregion
    }
}
